package xc.dao;

import xc.entity.Program;
import xc.utils.JdbcUtils;
import xc.utils.TransactionManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProgramDao {

    public static boolean existProgram(String pName){
        String sql = "select * from PROJECT where P_NAME = ?";
        try (Connection connection = JdbcUtils.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1,pName);
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean existProgram(Program program){
        String sql = "select * from PROJECT where P_NAME = ?";
        try (Connection connection = JdbcUtils.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1,program.getPName());
            ResultSet rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }





    public static void addProgram(Program program) throws SQLException {

        int pSale = Integer.parseInt(program.getPSale());
        int VipSale = pSale - 170;
        JdbcUtils jdbcUtils = new JdbcUtils();
        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
            TransactionManager.beginTransaction();
            String sql = "insert into PROJECT (P_NAME,P_SALE,VIP_SALE) values(?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,program.getPName());
            pstmt.setString(2,program.getPSale());
            pstmt.setString(3, String.valueOf(VipSale));
            int rs = pstmt.executeUpdate();
            if(rs>0){
                System.out.println("添加成功");
            }else  {
                System.out.println("添加失败");
            }
            TransactionManager.commit();
        }catch (Exception e){
            try {
                TransactionManager.rollback();
            }catch (SQLException ex) {
                ex.printStackTrace();
            }finally {
                JdbcUtils.closeResource(null, pstmt);
                TransactionManager.release();
            }
        }
    }







    public static void deleteProgram(String programName) throws SQLException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        JdbcUtils jdbcUtils = new JdbcUtils();

        try {
            TransactionManager.beginTransaction();
            String sql = "delete from PROJECT where P_NAME = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,programName);
            int rs = pstmt.executeUpdate();
            if(rs>0){
                System.out.println("删除成功");
            }else   {
                System.out.println("删除失败");
            }
            TransactionManager.commit();
        }catch (Exception e){
            try {
                TransactionManager.rollback();
            }catch (SQLException ex) {
                ex.printStackTrace();
            }finally {
                JdbcUtils.closeResource(null,pstmt);
                TransactionManager.release();
            }
        }
    }




    public static void updateProgram(Program program) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        JdbcUtils jdbcUtils = new JdbcUtils();
        int pSale = Integer.parseInt(program.getPSale());
        int VipSale = pSale - 170;

        try {
            TransactionManager.beginTransaction();
            conn = TransactionManager.getCurrentConnection();
            String sql = "update PROJECT set P_NAME = ?, P_SALE = ?, VIP_SALE = ? where P_NAME = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, program.getPName());
            pstmt.setString(2, program.getPSale());
            pstmt.setString(3, String.valueOf(VipSale));
            pstmt.setString(4, program.getPName());
            int rs = pstmt.executeUpdate();
            if (rs > 0) {
                System.out.println("修改成功");
            } else {
                System.out.println("修改失败");
            }
            TransactionManager.commit();
        } catch (Exception e) {
            try {
                TransactionManager.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }finally {
                JdbcUtils.closeResource(null,pstmt);
                TransactionManager.release();
            }
        }
    }





    public static void selectProgram() throws SQLException {
        JdbcUtils jdbcUtils = new JdbcUtils();
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            TransactionManager.beginTransaction();
            conn = TransactionManager.getCurrentConnection();
            String sql = "select P_NAME,P_SALE,VIP_SALE from PROJECT";
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                String PName = rs.getString("P_NAME");
                String PSale = rs.getString("P_SALE");
                String VipSale = rs.getString("VIP_SALE");
                System.out.println("项目名：" + PName + "  项目价格：" + PSale + "  会员价格：" +VipSale);
            }
            TransactionManager.commit();
        }catch (Exception e){
            try {
                TransactionManager.rollback();
            }catch (Exception ex) {
                ex.printStackTrace();
            }
        }finally {
            JdbcUtils.closeResource(null,pstmt);
            TransactionManager.release();
        }
    }
}
